﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;

using System.Data;
using System.IO;
using System.Linq;

namespace ShortUrl.Web.Tool
{
    public class ExcelToTable1
    {
        #region 读取Excel文件  
        /// <summary>  
        /// 读取Excel文件到table中  
        /// </summary>  
        /// <param name="filePath">excel文件路径</param>  
        /// <returns></returns>  
        public static DataTable ReadExcel(string fileName, string[] tablehead = null)
        {
            DataTable dt = new DataTable();
            //文件是否存在  
            if (System.IO.File.Exists(fileName))
            {
                dt = ImportExcelFile(fileName, tablehead);
            }
            return dt;
        }
        public static DataTable ImportExcelFile(string filePath, string[] tablehead = null)
        {
            IWorkbook hssfworkbook = null;
            DataTable dt = new DataTable();
            bool isDataEnd = false;
            #region//初始化信息  
            try
            {
                string[] fileEx = filePath.Split('.');

                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (fileEx[fileEx.Length - 1] == "xls")
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                    else if (fileEx[fileEx.Length - 1] == "xlsx")
                    {
                        hssfworkbook = new XSSFWorkbook(file);
                    }
                    NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    rows.MoveNext();
                    IRow row = (IRow)rows.Current;
                    int CellNum = (sheet.GetRow(0).LastCellNum);
                    for (int j = 0; j < CellNum; j++)
                    {
                        if (tablehead != null && tablehead.Length >= (sheet.GetRow(0).LastCellNum))
                        {
                            dt.Columns.Add(tablehead[j]);
                        }
                        else
                        {
                            //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());  
                            //将第一列作为列表头  
                            dt.Columns.Add(row.GetCell(j).ToString());
                        }
                    }
                    while (rows.MoveNext())
                    {
                        row = (IRow)rows.Current;

                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < row.LastCellNum; i++)
                        {
                            NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                            if (cell == null)
                            {
                                dr[i] = null;
                            }
                            else
                            {
                                dr[i] = cell.ToString();
                            }
                        }
                        if (IsEmpty(dr.ItemArray.ToList()))
                        {
                            isDataEnd = true;
                            break;
                        }
                        if (isDataEnd)
                        {
                            break;
                        }
                        else {
                            dt.Rows.Add(dr);
                        }

                    }

                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion

            return dt;
        }

        /// <summary>
        /// 检查读取的是不是空行
        /// </summary>
        /// <param name="ValueList">一行的值，保存在字符串数组中</param>
        /// <returns>布尔值</returns>
        public static bool IsEmpty(List<object> ValueList)
        {
            bool rt = true;

            foreach (object s in ValueList)
            {
                if (!String.IsNullOrEmpty(s.ToString()))
                {
                    rt = false;

                    break;
                }
            }

            return rt;
        }

        #endregion
    }
}
